﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace produccion.clases
{
    public class CLSMAEEST
    {
        string codref_est, codcol_est, codtal_est, codmtp_est, observ_est, tipmat_est;
        double canmtp_est;
        

        public string CODREF_EST
        {
            get { return codref_est; }
            set { codref_est = value; }
        }

        public string CODCOL_EST
        {
            get { return codcol_est; }
            set { codcol_est = value; }
        }

        public string CODTAL_EST
        {
            get { return codtal_est; }
            set { codtal_est = value; }
        }

        public string CODMTP_EST
        {
            get { return codmtp_est; }
            set { codmtp_est = value; }
        }

        public double CANMTP_EST
        {
            get { return canmtp_est; }
            set { canmtp_est = value; }
        }

        public string OBSERV_EST
        {
            get { return observ_est; }
            set { observ_est = value; }
        }

        public string TIPMAT_EST
        {
            get { return tipmat_est; }
            set { tipmat_est = value; }
        }
        
        MDOgeneral mod = new MDOgeneral();

        public bool Existe()
        {
            try
            {
                DataTable dt = new DataTable();
                string query = "select distinct codmtp_est from maeest inner join maerefp on codref_est=codref_ref where codref_ref='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "'";
                dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ActualizarMaterialEnRef(string MatNue)
        {
            try
            {                
                string query = "update maeest set codmtp_est='" + MatNue + "' where codref_est='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "' and codtal_est='" + CODTAL_EST + "' and codmtp_est='" + CODMTP_EST + "'";
                mod.comando(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool ExisteMaterialEnRef()
        {
            try
            {
                DataTable dt = new DataTable();
                string query = "select distinct codmtp_est from maeest inner join maerefp on codref_est=codref_ref where codref_ref='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "' and codtal_est='" + CODTAL_EST + "' and codmtp_est='" + CODMTP_EST + "'";
                dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Consultar()
        {
            try
            {
                DataTable Tal = new DataTable();
                CLSMAEPRO MP = new CLSMAEPRO();
                DataTable dt = new DataTable();
                string query = "select distinct codmtp_est as Material, nommat_mat as Descripcion, nomcol_col as Color, uniusu_mat as Uso, unicom_mat as Compra from maeest inner join maeref on codref_est=codref_ref inner join maemat on substr(codmtp_est,1,6)=codmat_mat inner join maecolp on substr(codmtp_est,7,4)=codcol_col where codref_est='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "' order by codmtp_est";
                dt = mod.consulta(query);
                MP.CODREF_PRO = CODREF_EST;
                MP.CODCOL_PRO = CODCOL_EST;
                Tal = MP.BuscarTallas();
                dt.Columns[1].ReadOnly = true;
                dt.Columns[2].ReadOnly = true;
                dt.Columns[3].ReadOnly = true;
                dt.Columns[4].ReadOnly = true;
                dt.Columns.Add(new DataColumn("Principal", System.Type.GetType("System.Boolean")));                
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < Tal.Rows.Count; i++)
                    {
                        dt.Columns.Add(new DataColumn(Tal.Rows[i][0].ToString(), System.Type.GetType("System.Double")));
                    } 
                    dt.Columns.Add(new DataColumn("Detalle", System.Type.GetType("System.String")));
                    return dt;
                }
                dt.Clear();
                query = "select '' as Material, '' as Descripcion, '' as Color, '' as Uso, '' as Compra from maeest where rownum=1";
                dt = mod.consulta(query);
                dt.Columns[1].ReadOnly = true;
                dt.Columns[2].ReadOnly = true;
                dt.Columns[3].ReadOnly = true;
                dt.Columns[4].ReadOnly = true;
                dt.Columns.Add(new DataColumn("Principal", System.Type.GetType("System.Boolean")));
                for (int i = 0; i < Tal.Rows.Count; i++)
                {

                    dt.Columns.Add(new DataColumn(Tal.Rows[i][0].ToString(), System.Type.GetType("System.Double")));
                }
                dt.Columns.Add(new DataColumn("Detalle", System.Type.GetType("System.String")));
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Consultar_SoloConsulta(String StrCodigoGrupo, String StrCodigoSubGrupo)
        {
            try
            {
                DataTable Tal = new DataTable();
                CLSMAEPRO MP = new CLSMAEPRO();
                DataTable dt = new DataTable();

                #region FJRF - EstoEsNuevoComparadoConElCodigoOriginal
                string query = "", strCampos = "", strCamposGrupo = "", strCamposSubGrupo = "", strTabla = "", StrCondicion = "", StrConsultaSql1 = "", StrConsultaSql2 = "";//FJRF - Las variables que vamos a usar

                strCampos = "TRIM(UPPER(MATERIAL)) AS MATERIAL,  TRIM(UPPER(DESCRIPCION)) AS DESCRIPCION,  TRIM(UPPER(COLOR)) AS COLOR,  TRIM(UPPER(USO)) AS USO,  TRIM(UPPER(COMPRA)) AS COMPRA";//FJRF - Los campos que necesita la consulta principal
                strCamposGrupo = "CODGRP,  GRUPO";//FJRF - Los campos para cargar el combo de grupos
                strCamposSubGrupo = "CODSUB,  SUBGRUPO ";//FJRF - Los campos para cargar los subgrupos
                strTabla = "FROM VIS_ESTANDARES ";//FJRF - La vista que contiene el select usado en el proceso original

                if (StrCodigoGrupo == "ZZZ999" && StrCodigoSubGrupo == "ZZZ999")//FJRF - Si grupo y subgrupo vienen vacios hacer lo siguiente
                {
                    StrCondicion = "where CODREF ='" + CODREF_EST + "' AND CODCOL ='" + CODCOL_EST + "'";//FJRF - Establecemos la condicion
                }
                else//FJRF - Si grupo o el subgrupo trae datos hacemo lo siguiente
                {
                    if (StrCodigoGrupo != "ZZZ999" && StrCodigoSubGrupo == "ZZZ999")//FJRF - Si solo trae datos el grupo hacemos lo siguiente
                    {
                        StrCondicion = "where CODREF ='" + CODREF_EST + "' AND CODCOL ='" + CODCOL_EST + "' AND CODGRP = '" + StrCodigoGrupo + "'";//FJRF - Establecemos la condicion
                    }
                    else
                    {
                        if (StrCodigoGrupo != "ZZZ999" && StrCodigoSubGrupo != "ZZZ999")//FJRF - Si el codigo del grupo y del subgrupo traen datos hacemos lo siguiente
                        {
                            StrCondicion = "where CODREF ='" + CODREF_EST + "' AND CODCOL ='" + CODCOL_EST + "' AND CODGRP = '" + StrCodigoGrupo + "'  AND CODSUB = '" + StrCodigoSubGrupo + "' ";//FJRF - Establecemos la condicion
                        }
                        else
                        {
                            if (StrCodigoGrupo == "ZZZ999" && StrCodigoSubGrupo != "ZZZ999")//FJRF - Si solo trae datos el sub grupo hacemos lo siguiente
                            {
                                StrCondicion = "where CODREF ='" + CODREF_EST + "' AND CODCOL ='" + CODCOL_EST + "' AND CODSUB = '" + StrCodigoSubGrupo + "' ";//FJRF - Establecemos la condicion
                            }
                        }
                    }
                }
                //FJRF - Nota: Si se modifica la consulta anterior tambien se debe modificar la vista segun los nuevos cambios q se efectuen

                StrConsultaSql1 = "SELECT " + strCamposGrupo + " " + strTabla + " " + StrCondicion + " " + "GROUP BY " + strCamposGrupo + " ORDER BY GRUPO ";//FJRF - Para trabajar esta consulta para cargar los combos de grupos
                MDOgeneral.strConsultasTemporalesSql1 = StrConsultaSql1;

                StrConsultaSql2 = "SELECT " + strCamposSubGrupo + " " + strTabla + " " + StrCondicion + " " + "GROUP BY " + strCamposSubGrupo + " ORDER BY SUBGRUPO ";//FJRF - Para trabajar esta consulta para cargar los combos de subgrupos
                MDOgeneral.strConsultasTemporalesSql2 = StrConsultaSql2;
                
                query = "SELECT " + strCampos + " " + strTabla + " " + StrCondicion + " ORDER BY MATERIAL";//FJRF - Para trabajar esta consulta general
                #endregion

                dt = mod.consulta(query);

                MP.CODREF_PRO = CODREF_EST;
                MP.CODCOL_PRO = CODCOL_EST;
                Tal = MP.BuscarTallas();
                dt.Columns[1].ReadOnly = true;
                dt.Columns[2].ReadOnly = true;
                dt.Columns[3].ReadOnly = true;
                dt.Columns[4].ReadOnly = true;
                dt.Columns.Add(new DataColumn("Principal", System.Type.GetType("System.Boolean")));
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < Tal.Rows.Count; i++)
                    {
                        dt.Columns.Add(new DataColumn(Tal.Rows[i][0].ToString(), System.Type.GetType("System.Double")));
                    }
                    dt.Columns.Add(new DataColumn("Detalle", System.Type.GetType("System.String")));
                    return dt;
                }
                dt.Clear();
                query = "select '' as Material, '' as Descripcion, '' as Color, '' as Uso, '' as Compra from maeest where rownum=1";
                dt = mod.consulta(query);
                dt.Columns[1].ReadOnly = true;
                dt.Columns[2].ReadOnly = true;
                dt.Columns[3].ReadOnly = true;
                dt.Columns[4].ReadOnly = true;
                dt.Columns.Add(new DataColumn("Principal", System.Type.GetType("System.Boolean")));
                for (int i = 0; i < Tal.Rows.Count; i++)
                {

                    dt.Columns.Add(new DataColumn(Tal.Rows[i][0].ToString(), System.Type.GetType("System.Double")));
                }
                dt.Columns.Add(new DataColumn("Detalle", System.Type.GetType("System.String")));
                
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable MaterialEstandar()
        {
            try
            {
                DataTable dt = new DataTable();
                //string query = "select distinct codmtp_est, material_ref  from maeest inner join maerefp on codref_est=codref_ref where codref_ref='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "'  order by material_ref";

                string query = "Select distinct codmtp_est, nommat_mat from maeest inner join maerefp on codref_est=codref_ref inner join maemat on substr(codmtp_est,0,6) = codmat_mat where codref_ref='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "'   order by nommat_mat ";

                dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Insertar()
        {
            try
            {                
                string query = "insert into maeest (codref_est,codcol_est,codtal_est,codmtp_est,canmtp_est,observ_est,tipmat_est) values";
                query = query + " ('" + CODREF_EST + "','" + CODCOL_EST + "','" + CODTAL_EST + "','" + CODMTP_EST + "'," + CANMTP_EST + ",'" + OBSERV_EST + "','" + TIPMAT_EST + "')";
                mod.comando(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Grabar(DataTable Tabla)
        {
            try
            {
                MDOgeneral.transaccion = MDOgeneral.conexion.BeginTransaction();
                Eliminar();
                for (int i = 0; i < Tabla.Rows.Count; i++)
                {
                    if (Tabla.Rows[i][0].ToString() != "")
                    {
                        for (int j = 6; j < Tabla.Columns.Count-1; j++)
                        {
                            if (Tabla.Rows[i][j].ToString() != "")
                            {
                                CODTAL_EST = Tabla.Columns[j].ColumnName;
                                CODMTP_EST = Tabla.Rows[i][0].ToString();
                                CANMTP_EST = System.Convert.ToDouble(Tabla.Rows[i][j].ToString());
                                TIPMAT_EST = Tabla.Rows[i][5].Equals(true) ? "P" : "S";
                                OBSERV_EST = Tabla.Rows[i][Tabla.Columns.Count - 1].ToString();
                                Insertar();
                            }
                        }
                    }
                }
                MDOgeneral.transaccion.Commit();
            }
            catch (Exception ex)
            {
                MDOgeneral.transaccion.Rollback();
                throw ex;
            }
        }

        public void Eliminar()
        {
            try
            {
                string query = "delete from maeest where codref_est='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "'";                
                mod.comando(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Actualizar()
        {
            try
            {
                string query = "update maeest set canmtp_est=" + CANMTP_EST + ", observ_est='" + OBSERV_EST + "', tipmat_est='" + TIPMAT_EST + "' where codref_est='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "' and codtal_est='" + CODTAL_EST + "' and codmtp_est='" + CODMTP_EST + "'";
                mod.comando(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public double CantidadEstandar()
        {
            try
            {
                DataTable dt = new DataTable();
                string query = "select canmtp_est from maeest inner join maerefp on codref_est=codref_ref where codref_ref='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "' and codtal_est='" + CODTAL_EST + "' and codmtp_est='" + CODMTP_EST + "'";
                dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return System.Convert.ToDouble(dt.Rows[0][0].ToString());
                }
                return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string Detalle()
        {
            try
            {
                DataTable dt = new DataTable();
                string query = "select observ_est from maeest where codref_est='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "' and codmtp_est='" + CODMTP_EST + "'";
                dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return (dt.Rows[0][0].ToString());
                }
                return "";
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool ExisteTallaEnEstandar()
        {
            try
            {
                DataTable dt = new DataTable();
                string query = "select codtal_est from maeest where codref_est='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "' and codtal_est='" + CODTAL_EST.Replace("'","''") + "' and codmtp_est='" + CODMTP_EST + "'";
                dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string Tipo()
        {
            try
            {
                DataTable dt = new DataTable();
                string query = "select distinct tipmat_est from maeest where codref_est='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "' and codmtp_est='" + CODMTP_EST + "'";
                dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return dt.Rows[0][0].ToString();
                }
                return "";
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public DataTable Estandar()
        {
            try
            {
                DataTable dt = new DataTable();
                string query = "select distinct codtal_est, codmtp_est, nommat_mat, canmtp_est from maeest inner join  where codref_est='" + CODREF_EST + "' and codcol_est='" + CODCOL_EST + "'";
                dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ConsultaMaterial()
        {
            try
            {
                DataTable dt = new DataTable();
                string query = "select distinct codref_est as Referencia, nomref_ref as Nombre, codcol_est as Color, codtal_est as Talla, canmtp_est as Cantidad from maeest INNER JOIN maeref on codref_est=codref_ref  where codmtp_est='" + CODMTP_EST + "'";
                dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}


